import psycopg2

def get_table_definitions(connection):
    cursor = connection.cursor()
    cursor.execute("""
        SELECT
          table_name,
          string_agg(
            column_name,
            ', '
            ORDER BY
              ordinal_position
          ) AS columns,
          max(table_schema) as schema_name
        FROM
          information_schema.columns
        WHERE
          table_schema = ANY (
                SELECT
                  schema_name
                FROM
                  information_schema.schemata
                where
                  schemata.schema_name in (
                    'sl'
                  )
              )
                GROUP BY
                  table_name
                ORDER BY
                  table_name
    """)
    table_definitions = cursor.fetchall()
    cursor.close()
    return table_definitions

def get_table_description(connection, table_name):
    cursor = connection.cursor()
    cursor.execute(f"""
    SELECT
      c.relname AS table_name,
      obj_description(c.oid) AS table_description
    FROM
      pg_class c
      LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE
      c.relkind = 'r'
      AND n.nspname NOT IN ('pg_catalog', 'information_schema')
      and c.relname='{table_name}'
    ORDER BY
      c.relname;
    """)
    row = cursor.fetchone()
    cursor.close()
    if row is None:
        return None
    return row[1]


def get_column_description(connection, table_name):
    cursor = connection.cursor()
    cursor.execute(f"""
    SELECT
      a.attname as ColumnName,
      col_description(a.attrelid, a.attnum) as ColumnComment
    FROM
      pg_attribute a
      JOIN pg_class b ON a.attrelid = b.oid
    WHERE
      b.relname = '{table_name}'
      AND a.attnum > 0
      AND NOT a.attisdropped
    ORDER BY
      a.attnum;
    """)
    column_description = cursor.fetchall()
    cursor.close()
    return column_description

def main():
    """
    输出表字段信息和表描述
    :return:
    """
    try:
        # 连接到 PostgreSQL 数据库
        connection = psycopg2.connect(
            dbname="sl",
            user="sl",
            password="201905@Smartlight",
            host="111.14.212.184",
            port="8004"
        )

        # 获取所有表的结构定义
        table_definitions = get_table_definitions(connection)
        table_definitions_demo = ['b_alonelamp_effect', 'b_canton', 'b_hitch_dic', 'b_lampplanstatusreal', 'b_lampplanstatusreal_detail', 'b_lamppost_type', 'r_sys_overview', 'sys_onoff_time', 'b_loctype', 'b_organ', 'b_street', 'b_street_side', 'b_street_street_side', 'b_sunrise_set', 'b_work_area', 'energy_saving_rate', 'l_alarm_coplog', 'l_alarm_enable', 'l_alone_lamp_special', 'l_alonelamp', 'l_alonelamp_control', 'l_alonelamp_last_elecparam', 'l_alonelamp_property', 'l_control_unit', 'l_coplog', 'l_event_log', 'l_his_coplog', 'l_his_hitch_alarm', 'l_his_hitch_alarm_report', 'l_hitch_alarm', 'l_hitch_alarm_report', 'l_lamp_match_rate', 'l_lamp_model', 'l_lamp_model_alonelamp', 'l_lamp_online_rate', 'l_lamp_online_rate_detail', 'l_lamppost', 'l_onoff_plan', 'l_onoff_plan_cmd_state', 'l_onoff_plan_command_send_state', 'l_onoff_plan_send', 'l_onoff_time', 'l_open_rate_history', 'l_program_detail_results', 'l_program_result_list', 'l_single_lamp_alarm', 'open_rate', 'open_rate_detail', 'r_hitch_alarm_coplog', 'b_scheme_onoff_command_send_state', 'b_scheme_onoff_plan_send', 'b_scheme_onoff_time_rtu', 'b_scheme_preview']

        # 输出查询结果
        output = []
        for table_definition in table_definitions:
            table_name, columns, schema_name = table_definition
            table_descriptions = get_table_description(connection, table_name)
            if table_name in table_definitions_demo:
                column_descriptions = get_column_description(connection, table_name)

                # 输出的是表字段信息的描述
                if column_descriptions is not None:
                    for column_description in column_descriptions:
                        column_name, column_comment = column_description
                        if column_comment is not None:
                            #print("字段："+column_name+"，表示的是："+column_comment)
                            output.append(column_name+"："+column_comment)

                #输出的是表的描述信息及表中包含的字段信息
                # if table_descriptions is not None:
                #     table={table_descriptions:schema_name+'.'+table_name,'字段信息':columns}
                #     print(table)
                #     #output.append((table_name, columns))
                #     output.append(table)
        #
        # 输出结果
        print(output)

        # 关闭数据库连接
        connection.close()
    except psycopg2.Error as e:
        print("Error connecting to PostgreSQL database:", e)

if __name__ == "__main__":
    main()




